<?php 
$select_fields = array();
$join_tables = array();
$indirect_joins = array();
$add_joins = array();

foreach ($app_display_names as $section_name=>$section_fields)
    foreach ($section_fields as $field=>$name) {
        $f = new DisplayField($field, $name);
        if (!is_shown($f)) continue;
        $type = explode("#", $app_fields[$f->table][$f->field]);
        if (!in_array($f->table, $join_tables))
            array_push($join_tables, $f->table);
        if ($f->indirection == null) {
            if ($f->where == null) {
                array_push($select_fields, array($f->table.".".$f->field, $f->get_show_checkbox_name()));
                if ($type[0] == "string" && count($type) == 3 && $type[2][0] == "*") {
                    $info = explode("*",substr($type[2],1));
                    $v_table = $info[0];
                    $v_field = $info[1];
                    $v_join_field = $info[2];
                    $v_join_table_field = $info[3];
                    array_push($select_fields, array($f->table.".".$v_join_field,$f->get_show_checkbox_name()."\$\$JOIN_FIELD"));
                }
            } else if ($section_name <> "Exam Results") { // sql request too long with all joins of results
                $join_alias = "addjoin".count($add_joins);
                $join_condition = "(".$join_alias.".applicant_id=applicant.applicant_id";
                foreach ($f->where as $key=>$value)
                    $join_condition .= " AND ".$join_alias.".".$key."=".DataBase::getValue($value);
                $join_condition .= ")";
                array_push($add_joins, array($f->table,$join_alias,$join_condition));
                array_push($select_fields, array($join_alias.".".$f->field, $f->get_show_checkbox_name()));
            }
        } else {
            handle_indirection($f->table, $f->field, $f->indirection, $f->get_show_checkbox_name(), null);
        }
    }
for ($i_filter = 0; $i_filter < count($filters); $i_filter++) {
    $filter = $filters[$i_filter];
    switch ($filter["type"]) {
        case "column":
            do {
                $field = $filter["field"];
                $condition = $filter["condition"];
                $parts = explode("\$",$field);
                if (count($parts)<2) continue;
                // check if is a where field
                $where_field = null;
                foreach ($app_display_names as $section_name=>$section_fields) {
                    foreach ($section_fields as $f=>$name) {
                        if (str_replace(".","$",str_replace("/","$",$f)) == $field && strpos($f,"/") !== false) {
                            $where_field = new DisplayField($f, $name);
                            break;
                        }
                    }
                    if ($where_field<>null) break;
                }
                $table = $parts[0];
                $field = $parts[1];
                if ($where_field == null) {
                    // if the filter is on date or time, we need the indirection
                    if (count($parts)>2) {
                        $found = false;
                        foreach ($app_display_names as $section_name=>$section_fields) {
                            foreach ($section_fields as $field=>$name) {
                                $f = new DisplayField($field, $name);
                                if ($f->get_show_checkbox_name() == "field__".$filter["field"]) {
                                    $found = true;
                                    if ($f->indirection <> null) {
                                        $type = explode("#", $app_fields[$f->table][$f->field]);
                                        $indirect_table = $type[1];
                                        $indirect_field = $type[2];
                                        $type = explode("#", $app_fields[$indirect_table][$f->indirection]);
                                        if ($type[0] == "date" || $type[0] == "time")
                                            $filters[$i_filter]["alias"] = handle_indirection($f->table, $f->field, $f->indirection, $f->get_show_checkbox_name(), null);
                                    }
                                    break;
                                }
                            }
                            if ($found) break;
                        }
                    }
                    if (!in_array($table, $join_tables))
                        array_push($join_tables, $table);
                } else {
                    $join_condition = "(##ALIAS##.applicant_id=applicant.applicant_id";
                    foreach ($where_field->where as $key=>$value)
                        $join_condition .= " AND ##ALIAS##.".$key."=".DataBase::getValue($value);
                    $join_condition .= ")";
                    $join_present = null;
                    foreach ($add_joins as $add_join) {
                        if ($add_join[0] <> $table) continue;
                        if (str_replace("##ALIAS##", $add_join[1], $join_condition) == $add_join[2]) {
                            $join_present = $add_join;
                            break;
                        }
                    }
                    if ($join_present == null) {
                        $join_alias = "addjoin".count($add_joins);
                        $join_condition = str_replace("##ALIAS##", $join_alias, $join_condition);
                        array_push($add_joins, array($table,$join_alias,$join_condition));
                    }
                }
                $filter = $filter["or"];
            } while ($filter <> null);
            break;
    }
}
function handle_indirection($table, $field, $remaining_indirection, $field_name, $indirect_alias) {
    global $indirect_joins, $select_fields, $app_fields;
    $type = explode("#", $app_fields[$table][$field]);
    if ($type[0] <> "id") {
        echo "Unexpected indirection";
        return;
    }
    $indirect_table = $type[1];
    $indirect_field = $type[2];
    $alias = null;
    foreach ($indirect_joins as $ij) {
        if ($ij[0] == ($indirect_alias <> null ? $indirect_alias : $table) &&
                $ij[1] == $field &&
                $ij[2] == $indirect_table &&
                $ij[3] == $indirect_field
        ) {
            $alias = $ij[4];
            break;
        }
    }
    if ($alias == null) {
        $alias = "join".count($indirect_joins);
        array_push($indirect_joins, array($indirect_alias <> null ? $indirect_alias : $table,$field,$indirect_table,$indirect_field, $alias));
    }
    array_push($select_fields, array(($indirect_alias <> null ? $indirect_alias : $table).".".$field, $field_name."\$\$ID"));
    $i = strpos($remaining_indirection, "#");
    if ($i > 0) {
        $indirection = substr($remaining_indirection, 0, $i);
        $remaining_indirection = substr($remaining_indirection, $i+1);
    } else {
        $indirection = $remaining_indirection;
        $remaining_indirection = null;
    }
    array_push($select_fields, array($alias.".".$indirection, $field_name."$".$indirection));
    if ($remaining_indirection <> null)
        handle_indirection($indirect_table, $indirection, $remaining_indirection, $field_name."$".$indirection, $alias);
    return $alias;
}

$query = "SELECT DISTINCT applicant.applicant_id";
foreach ($select_fields as $a) {
    $query .= ",".$a[0]." AS ".$a[1];
}
$query .= " FROM applicant";
foreach ($join_tables as $table)
    if ($table <> "applicant")
        $query .= " LEFT JOIN ".$table." ON ".$table.".applicant_id=applicant.applicant_id";
foreach ($indirect_joins as $join)
    $query .= " LEFT JOIN ".$join[2]." AS ".$join[4]." ON ".$join[4].".".$join[3]."=".$join[0].".".$join[1];
foreach ($add_joins as $join)
    $query .= " LEFT JOIN ".$join[0]." AS ".$join[1]." ON ".$join[2];
$where = "";
if (!isset($_POST["include_excluded"])) 
    $where .= " applicant.excluded=0";
foreach ($filters as $filter) {
    switch ($filter["type"]) {
        case "column":
            $w = "";
            do {
                $field = $filter["field"];
                $condition = $filter["condition"];
                $parts = explode("\$",$field);
                if (count($parts)<2) {
                    echo "Invalid filter field: ".text2html($field)."<br/>";
                    continue;
                }
                // check if is a where field
                $where_field = null;
                foreach ($app_display_names as $section_name=>$section_fields) {
                    foreach ($section_fields as $f=>$name) {
                        if (str_replace(".","$",str_replace("/","$",$f)) == $field && strpos($f,"/") !== false) {
                            $where_field = new DisplayField($f, $name);
                            break;
                        }
                    }
                    if ($where_field<>null) break;
                }
                $table = $parts[0];
                $field = $parts[1];
                if ($w<>"") $w .= " OR ";
                if ($where_field == null) {
                    if (isset($filter["alias"])) {
                        $w .= $filter["alias"].".".$parts[count($parts)-1].$condition;
                    } else
                        $w .= $table.".".$field.$condition;
                } else {
                    $join_condition = "(##ALIAS##.applicant_id=applicant.applicant_id";
                    foreach ($where_field->where as $key=>$value)
                        $join_condition .= " AND ##ALIAS##.".$key."=".DataBase::getValue($value);
                    $join_condition .= ")";
                    $join_present = null;
                    foreach ($add_joins as $add_join) {
                        if ($add_join[0] <> $table) continue;
                        if (str_replace("##ALIAS##", $add_join[1], $join_condition) == $add_join[2]) {
                            $join_present = $add_join;
                            break;
                        }
                    }
                    if ($join_present == null) {
                        // ??? should be present from query build
                        echo "Unexpected case: cannot find 'join' in request for filter with 'where' field!<br/>";
                    } else {
                        $w .= $join_present[1].".".$field.$condition;
                    }
                }
                $filter = $filter["or"];
            } while ($filter <> null);
            if ($where<>"") $where .= " AND ";
            $where .= "(".$w.")";
            break;
    }
}
if (strlen($where)>0) $where .= " AND ";
$where .= "applicant.selection_campaign_id=".$_SESSION["campaign_id"];
if (strlen($where)>0)
    $query .= " WHERE ".$where;
//if ($sort <> null)
//	$query .= " ORDER BY ".$sort;
$query .= " ORDER BY applicant.applicant_id ASC";
$total_applicant = count(DataBase::query($query));
if(isset($_GET['view']) && ($_GET['view'] == 'from_data_entry_operators' || $_GET['view'] == 'general'))
    $query .= " LIMIT 0,100";

if (isset($_SESSION["debug"]) && $_SESSION["debug"]=="yes") {
    echo "<br/><br/>DEBUG:<br/>";
    echo "QUERY=".$query;
    echo "<br/><br/>";
    $query_start_time = microtime(true);
}
flush();
$result = DataBase::query($query);
//echo '<pre>'.$query.'</pre>';
//echo "<br/>QUERY:<br/>".$query."<br/><br/>RESULT:<br/>".($result === false ? "FALSE: Error #".DataBase::last_error_number().": ".DataBase::last_error_message() : count($result)." rows")."<br/><br/>";

if (isset($_SESSION["debug"]) && $_SESSION["debug"]=="yes") {
    $query_end_time = microtime(true);
    echo "QUERY TIME=".($query_end_time-$query_start_time)."s.<br/>";
}

if (!isset($_POST["export"]) || $_POST["export"]=="no") {
    if (isset($_POST["additional_code"])) include($_SESSION["root_path"]."/".$_POST["additional_code"]);
    ?>
<script type='text/javascript'>
    function getAbsoluteOffsetTop(elem) {
        if (elem.offsetParent != null)
            return getAbsoluteOffsetTop(elem.offsetParent) + elem.offsetTop;
        return elem.offsetTop;
    }
    window.onscroll = function() {
        var st = element('table_header_static');
        var sc = element('table_header_scroll');
        if (document.body.scrollTop > getAbsoluteOffsetTop(st)) {
            sc.style.visibility = 'visible';
            sc.style.top = document.body.scrollTop+'px';
            for (var i = 0; i < st.childNodes.length; ++i)
                sc.childNodes[i].style.width = (st.childNodes[i].offsetWidth-2-4) + 'px';
        } else {
            sc.style.visibility = 'hidden';
        }
    }
</script>
<form name='applicants_table' action="<?php echo $_SESSION['root_url']?>/applicants/applicants_save.php" method="post" target="applicants_save" onsubmit="var e = document.all ? document.all['from_save_button'] : document.getElementById('from_save_button'); return e != null;">
    <input type='hidden' name='from_save' value='yes'/>
    <input type='hidden' name='timestamp' value='<?php echo time()?>'/>
        <?php
        echo "<div style='margin:0px;padding:0px'><span>";
        if ($result == false) echo "0"; else echo $total_applicant;
        echo " applicant(s) corresponding to your criteria:</span>";
        echo "<span style='margin-left:50px'>";
        echo "<button type='submit' id='save_button1' name='save_button' disabled='disabled' onclick='prepare_save();return false'><img src='".$_SESSION["root_url"]."/images/save.gif' hspace=2 vspace=0 style='vertical-align:bottom'/>Save</button>";
        echo "<button onclick=\"var f = document.forms['fields_selection'];f.elements['zoom'].value++;f.submit();return false\"><img src='".$_SESSION["root_url"]."/images/increase_font.gif'/></button>";
        echo "<button onclick=\"var f = document.forms['fields_selection'];f.elements['zoom'].value--;f.submit();return false\"><img src='".$_SESSION["root_url"]."/images/decrease_font.gif'/></button>";
        echo "</span>";
        echo "</div>";
    }
    if (!isset($zoom)) $zoom = 10;
    echo "<table class='applicants_table' cellspacing=0 cellpadding=0 style='font-size:".$zoom."pt'>";
    $header = "";
    if (isset($_POST["can_remove"])) $header .= "<th></th>";
    foreach ($app_display_names as $section_name=>$fields) {
        foreach ($fields as $field=>$name) {
            $f = new DisplayField($field, $name);
            if (is_shown($f)) {
                $header .= "<th nowrap='nowrap'>";
                $header .= $f->display_name;
                if (!(isset($_POST["export"]) && $_POST["export"]=="excel")) {
                    $header .= " <a href='' style='margin:0px;padding:0px;border:1px solid #404040;background-color:#C0C0C0' onclick=\"toggle_filter('".$f->get_show_checkbox_name()."');return false\"><img src='".$_SESSION["root_url"]."/images/".(has_filter($f)?"remove_filter":"filter").".gif' hspace=0 vspace=0 style='margin:0px;padding:0px;vertical-align:middle' border=0/></a>";
                }
                $header .= "</th>";
            }
        }
    }
    echo "<tr id='table_header_static'>".$header."</tr>";
    if (!isset($_POST["export"]) || $_POST["export"]=="no")
        echo "<tr id='table_header_scroll' style='visibility:hidden;position:absolute;border:1px solid black'>".$header."</tr>";

    if ($result <> false) {
        $row_number = 0;
        foreach ($result as $row) {
            if (($row_number%100)==0) flush();
            echo "<tr class='row_".(($row_number++%2)==0?"even":"odd")."'>";
            //if (isset($_POST["can_remove"])) echo "<td><a href='".$_SESSION["root_url"]."/applicants/remove.php?id=".$row["applicant_id"]."'>Remove</a></td>";
            if (isset($_POST["can_remove"])) echo "<td><a href='' onclick='remove_applicant(this, ".$row["applicant_id"].");return false'>Remove</a></td>";
            foreach ($app_display_names as $section_name=>$fields) {
                foreach ($fields as $field=>$name) {
                    $f = new DisplayField($field, $name);
                    if (!is_shown($f)) continue;
                    // handle exam results special case
                    if ($section_name == "Exam Results") {
                        if (!isset($row[$f->get_show_checkbox_name()])) {
                            $sq = "SELECT ".$f->field." AS ".$f->get_show_checkbox_name()." FROM ".$f->table." WHERE applicant_id=".$row["applicant_id"];
                            foreach ($f->where as $key=>$value)
                                $sq .= " AND ".$key."=".DataBase::getValue($value);
                            $sr = DataBase::query($sq);
                            $value = null;
                            if ($sr <> false && count($sr) > 0) $value = $row[$f->get_show_checkbox_name()] = $sr[0][$f->get_show_checkbox_name()];
                        }
                    } else {
                        // here we can have the name of the field ending with the indirection name in case of ID
                        if ($f->indirection <> null)
                            $value = $row[$f->get_show_checkbox_name()."$".str_replace("#", "$", $f->indirection)];
                        else
                            $value = $row[$f->get_show_checkbox_name()];
                    }
                    echo "<td nowrap='nowrap'>";
                    $type = explode("#",$app_fields[$f->table][$f->field]);
                    if (!has_edit($f) || (isset($_POST["export"]) && $_POST["export"]=="excel")) {
                        switch ($type[0]) {
                            case 'int':
                                if (count($type)==3) {
                                    // there are string values corresponding to each integer value
                                    $values = explode(",",$type[2]);
                                    $min_max = explode(",",$type[1]);
                                    $value = intval($value);
                                    if ($value >= intval($min_max[0]) && $value <= intval($min_max[1]))
                                        $value = $values[$value+intval($min_max[0])];
                                    else
                                        $value = "Invalid value ".$value." (must be an integer between ".$min_max[0]." and ".$min_max[1].")";
                                }
                                break;
                            case 'bool':
                                $value = "<input type='checkbox' disabled='disabled'".($value == 1 ? " checked='checked'" : "")."/>";
                        }
                        if ($f->is_linked())
                            echo "<span id='link__".$f->get_show_checkbox_name()."__".$row_number."'>";
                        if (isset($_POST["export"]) && $_POST["export"]=="excel" && $type[0] == "string" && is_numeric($value))
                            echo "&nbsp;";
                        echo $value;
                        if ($f->is_linked())
                            echo "</span>";
                    } else {
                        $final_table = $f->table;
                        $final_field = $f->field;
                        $final_key_field = "applicant_id";
                        $final_key_value = $row["applicant_id"];
                        if ($type[0] == 'id') {
                            $indirect_table = $type[1];
                            $indirect_join = $type[2];
                            $indirect_field = $f->indirection;

                            while (($i = strpos($indirect_field, "#")) > 0) {
                                $if = substr($indirect_field, 0, $i);
                                $type = explode("#", $app_fields[$indirect_table][$if]);
                                $indirect_table = $type[1];
                                $indirect_join = $type[2];
                                $indirect_field = substr($indirect_field, $i+1);
                            }

                            // Retrieve all possible values from referenced table
                            $values = get_table_values($indirect_table);
                            $choices = array();
                            foreach ($values as $val)
                                $choices[$val[$indirect_join]] = $val[$indirect_field];
                            $type = array("choice",$choices);

//						$final_table = $indirect_table;
//						$final_field = $indirect_field;
//						$final_key_field = $indirect_join;
//						$final_key_value = $row[$f->get_show_checkbox_name()."\$\$ID"];
//						
//						$type = explode("#",$app_fields[$final_table][$final_field]);
                        }
                        $form_field_name = $final_table."$".$final_field."$".$final_key_field."$".$final_key_value;
                        if ($f->where <> null) {
                            foreach ($f->where as $wk=>$wv)
                                $form_field_name .= "~".$wk."~".$wv;
                        }
                        switch ($type[0]) {
                            case "string":
                                if (count($type)==2)
                                    echo "<input type='text' size=".($type[1]>30?30:$type[1])." maxlength=".$type[1]." onchange=\"field_change('".$form_field_name."',".$row_number.")\" name='".$form_field_name."' value='".text2html($value)."'/>";
                                else {
                                    if ($type[2][0]=='*') {
                                        $info = explode("*",substr($type[2],1));
                                        $v_table = $info[0];
                                        $v_field = $info[1];
                                        $v_join_field = $info[2];
                                        $v_join_table_field = $info[3];

                                        $res_values = DataBase::query("SELECT ".$v_field." FROM ".$v_table." WHERE ".DataBase::where(array($v_join_table_field=>$row[$f->get_show_checkbox_name()."\$\$JOIN_FIELD"])));
                                        $values = array();
                                        if ($res_values <> false)
                                            foreach ($res_values as $res_value) array_push($values, $res_value[0]);
                                    } else {
                                        $values = explode(",",$type[2]);
                                    }
                                    echo "<select onchange=\"field_change('".$form_field_name."',".$row_number.")\" name='".$form_field_name."'>";
                                    echo "<option value='NULL'";
                                    if ($value == null || strlen($value)==0) echo " selected='selected'";
                                    echo "></option>";
                                    foreach ($values as $v) {
                                        echo "<option value='".$v."'";
                                        if ($value == $v) echo " selected='selected'";
                                        echo ">".$v."</option>";
                                    }
                                    echo "</select>";
                                }
                                break;
                            case "choice":
                                $id = $row[$f->get_show_checkbox_name()."\$\$ID"];
                                echo "<select onchange=\"field_change('".$form_field_name."',".$row_number.")\" name='".$form_field_name."'>";
                                echo "<option value='NULL'";
                                if ($id == null || strlen($id)==0) echo " selected='selected'";
                                echo "></option>";
                                foreach ($type[1] as $choice_id=>$choice_value) {
                                    echo "<option value='".$choice_id."'";
                                    if ($id == $choice_id) echo " selected='selected'";
                                    echo ">".text2html($choice_value)."</option>";
                                }
                                echo "</select>";
                                break;
                            case "int":
                                if (count($type) == 3) {
                                    // there are string values corresponding to each integer value
                                    $values = explode(",",$type[2]);
                                    $min_max = explode(",",$type[1]);
                                    $value = intval($value);
                                    echo "<select onchange=\"field_change('".$form_field_name."',".$row_number.")\" name='".$form_field_name."'>";
                                    for ($i = 0; $i < count($values); $i++) {
                                        echo "<option value='".(intval($min_max[0])+$i)."'";
                                        if ($value == intval($min_max[0])+$i) echo " selected='selected'";
                                        echo "'>".$values[$i]."</option>";
                                    }
                                    echo "</select>";
                                } else {
                                    echo "<input type='text' size=5 name='".$form_field_name."' value='".$value."' onchange=\"field_change('".$form_field_name."',".$row_number.")\" onkeydown='return validate_int(this,event);'/>";
                                }
                                break;
                            case "double":
                                echo "<input type='text' size=10 name='".$form_field_name."' value='".$value."' onchange=\"field_change('".$form_field_name."',".$row_number.")\" onkeydown='return validate_double(this,event);'/>";
                                break;
                            case "bool":
                                echo "<input type='checkbox' onchange=\"field_change('".$form_field_name."',".$row_number.",'bool')\" name='bool__".$form_field_name."' ".($value == 1 ? "checked='checked' " : "")."/>";
                                echo "<input type='hidden' name='".$form_field_name."' value='".($value == 1 ? 1 : 0)."'/>";
                                break;
                            case "date":
                                echo "<input type='text' size=10 readonly='readonly' value='".$value."' onclick=\"displayDatePicker('".$form_field_name."')\" onchange=\"field_change('".$form_field_name."',".$row_number.")\" name='".$form_field_name."'/>";
                                break;
                            default:
                                echo "Unknown field type: ".$type[0];
                        }
                    }
                    echo "</td>";
                }
            }
            echo "</tr>";
    }
}

echo "</table>";
if (!isset($_POST["export"]) || $_POST["export"]=="no") {
    echo "<button type='submit' id='save_button2' name='save_button' disabled='disabled' onclick='prepare_save();return false'><img src='".$_SESSION["root_url"]."/images/save.gif' hspace=2 vspace=0 style='vertical-align:bottom'/>Save</button>";
    echo "</form>";
}
?>
<?php if (isset($_POST["can_remove"])) { ?>
    <script type='text/javascript'>
        function remove_applicant(link, applicant_id) {
            if (!confirm("Are you sure you want to remove this applicant from the DataBase ?")) return;
            var frame = document.all ? document.all["frame_applicants_save"] : document.getElementById("frame_applicants_save");
            frame.onload = function() {
                var td = link.parentNode;
                var tr = td.parentNode;
                var table = tr.parentNode;
                table.removeChild(tr);
                frame.onload = null;
            }
            frame.src = "<?php echo $_SESSION["root_url"]?>/applicants/remove.php?applicant_id="+applicant_id;
        }
    </script>
        <?php } ?>
    <?php
    if (isset($_SESSION["debug"]) && $_SESSION["debug"]=="yes") {
        echo "<br/><br/>DEBUG:<br/>";
        echo "TABLES=";
        var_dump($join_tables);
        echo "<br/><br/>";
        echo "FIELDS=";
    var_dump($select_fields);
    echo "<br/><br/>";
    echo "QUERY=".$query;
    echo "<br/><br/>";
    echo "RESULT=";
    var_dump($result);
    echo "<br/><br/>";
}
?>
